package com.inei.cie.dao;

import java.util.ArrayList;
import java.util.List;

import android.content.ContentValues;
import android.content.Context;
import android.database.Cursor;
import android.database.sqlite.SQLiteDatabase;

import com.inei.cie.entidades.P5;
import com.inei.cie.entidades.P5_F;
import com.inei.cie.entidades.P5_N;
import com.inei.cie.utilitarios.ListadoAmbientesPorEdificacionAdapter;
import com.inei.cie.utilitarios.ListadoPisosAdapter;

public class CapituloVDao {
	private UtilDB util;
	SQLiteDatabase db;
	Cursor cursor = null;

	public CapituloVDao(Context context) {
		util = new UtilDB(context);
	}

	public int cantidadPisos(String id_local, String nroPredio) {
		String[] args = new String[] { id_local, nroPredio };
		SQLiteDatabase bd = util.getReadableDatabase();
		cursor = bd.rawQuery(
				"select * from P5_F where id_local=? and Nro_Pred=?", args);
		int cantidadPisos = 0;
		// if (cursor.moveToFirst()) {
		cantidadPisos = cursor.getCount();
		// }
		bd.close();
		cursor.close();
		return cantidadPisos;
	}

	public void crearP5Edificaciones(String id_local, int nroPredio) {
		SQLiteDatabase bd = util.getWritableDatabase();
		if (!existeP5Local(id_local, nroPredio)) {
			try {
				bd = util.getWritableDatabase();
				bd.execSQL("INSERT INTO P5(id_local,Nro_Pred,Version,SwCompleto,SwEnviado) VALUES('"
						+ id_local + "'," + nroPredio + "," + 1 + ",1,0)");
			} catch (Exception ex) {
				String h = ex.getMessage();
			}
		}
		bd.close();
	}

	public boolean existeP5Local(String id_local, int Nro_Pred) {
		SQLiteDatabase bd = util.getReadableDatabase();
		Cursor cursor = bd.rawQuery(
				"SELECT * FROM P5 WHERE id_local=? and Nro_Pred=?",
				new String[] { id_local, Nro_Pred + "" });
		if (cursor.moveToFirst()) {
			bd.close();
			return true;
		} else {
			bd.close();
			return false;
		}
	}

	public P5 obtenerP5Local(String id_local, int P5_NroPred) {
		P5 obj = null;
		SQLiteDatabase bd = util.getReadableDatabase();
		Cursor cursor = bd.rawQuery(
				"SELECT * FROM P5 WHERE id_local=? AND Nro_Pred=?",
				new String[] { id_local + "", P5_NroPred + "" });
		if (cursor.moveToFirst()) {
			while (!cursor.isAfterLast()) {
				obj = new P5();
				obj.setId_local(id_local);
				obj.setP5_Tot_E(cursor.getInt(cursor.getColumnIndex("P5_Tot_E")));
				obj.setP5_Tot_P(cursor.getInt(cursor.getColumnIndex("P5_Tot_P")));
				obj.setP5_Tot_LD(cursor.getInt(cursor
						.getColumnIndex("P5_Tot_LD")));
				obj.setP5_Tot_CTE(cursor.getInt(cursor
						.getColumnIndex("P5_Tot_CTE")));
				obj.setP5_Tot_MC(cursor.getInt(cursor
						.getColumnIndex("P5_Tot_MC")));
				obj.setP5_Tot_L1(cursor.getInt(cursor
						.getColumnIndex("P5_Tot_L1")));
				obj.setP5_Tot_R(cursor.getInt(cursor.getColumnIndex("P5_Tot_R")));
				obj.setP5_Opin(cursor.getString(cursor
						.getColumnIndex("P5_Opin")));
				obj.setSwCompleto(cursor.getInt(cursor
						.getColumnIndex("SwCompleto")) == 1 ? true : false);
				obj.setP5_Obs(cursor.getString(cursor.getColumnIndex("P5_Obs")));

				/*
				 * obj.setP5_Fenvio(Date.valueOf(cursor.getString(cursor
				 * .getColumnIndex("P5_Fenvio"))));
				 * obj.setP5_Frecep(Date.valueOf(cursor.getString(cursor
				 * .getColumnIndex("P5_Frecep"))));
				 */
				obj.setNro_Pred(P5_NroPred);
				cursor.moveToNext();
			}
		}
		bd.close();
		cursor.close();
		return obj;
	}

	public void actualizarP5Edificacion(P5 obj) {
		SQLiteDatabase bd = util.getWritableDatabase();
		ContentValues valores = new ContentValues();
		valores.put("P5_Tot_E", obj.getP5_Tot_E());
		valores.put("P5_Tot_P", obj.getP5_Tot_P());
		valores.put("P5_Tot_LD", obj.getP5_Tot_LD());
		valores.put("P5_Tot_CTE", obj.getP5_Tot_CTE());
		valores.put("P5_Tot_MC", obj.getP5_Tot_MC());
		valores.put("P5_Tot_L1", obj.getP5_Tot_L1());
		valores.put("P5_Tot_R", obj.getP5_Tot_R());
		valores.put("SwCompleto", obj.isSwCompleto());
		valores.put("P5_Opin", obj.getP5_Opin());
		valores.put("P5_Obs", obj.getP5_Obs());

		int ind = bd
				.update("P5",
						valores,
						"id_local=? and Nro_Pred=?",
						new String[] { obj.getId_local() + "",
								obj.getNro_Pred() + "" });
		bd.close();
	}

	public void actualizarP5_NEdificacion(List<P5_N> obj) {
		SQLiteDatabase bd = util.getWritableDatabase();
		for (P5_N p5_N : obj) {
			ContentValues valores = new ContentValues();
			valores.put("P5_TotAmb", p5_N.getP5_TotAmb());
			valores.put("SwCompleto", p5_N.isSwCompleto());
			int ind = bd
					.update("P5_N",
							valores,
							"id_local=? and Nro_Pred=? and P5_NroPiso=? and P5_Ed_Nro=?",
							new String[] { p5_N.getId_local() + "",
									p5_N.getNro_Pred() + "",
									p5_N.getP5_NroPiso() + "",
									p5_N.getP5_Ed_Nro() + "" });
		}
		bd.close();
	}

	public void insertarP5_FImagen(P5_F objF) {

		if (existeP5_FImagen(objF)) {
			SQLiteDatabase bd = util.getWritableDatabase();
			ContentValues valores = new ContentValues();
			valores.put("id_local", objF.getId_local());
			valores.put("P5_NroPiso", objF.getP5_NroPiso());
			valores.put("Nro_Pred", objF.getNro_Pred());
			valores.put("P5_Foto", objF.getP5_Foto());
			valores.put("P5_Escala", objF.getP5_Escala());
			bd.insert("P5_F", null, valores);
			bd.close();
		} else {
			SQLiteDatabase bd = util.getWritableDatabase();
			ContentValues valores = new ContentValues();
			valores.put("id_local", objF.getId_local());
			valores.put("P5_NroPiso", objF.getP5_NroPiso());
			valores.put("P5_NroPred", objF.getNro_Pred());
			valores.put("P5_Foto", objF.getP5_Foto());
			valores.put("P5_Escala", objF.getP5_Escala());
			long id = bd.insert("P5_F", null, valores);
			bd.close();
		}
	}

	public boolean existeP5_FImagen(P5_F objF) {
		SQLiteDatabase bd = util.getReadableDatabase();
		Cursor cursor = bd
				.rawQuery(
						"SELECT * FROM P5_N WHERE id_local= ? AND Nro_Pred= ? AND P5_NroPiso= ?",
						new String[] { objF.getId_local() + "",
								objF.getNro_Pred() + "",
								objF.getP5_NroPiso() + "" });
		if (cursor.moveToFirst()) {
			bd.close();
			return true;
		} else {
			bd.close();
			return false;
		}
	}

	public void crearP5_NEdificaciones(String id_local, int P5_NroPred,
			int p5_NroPiso, int count) {
		// boolean g = existeP5_NLocal(id_local, P5_NroPred, p5_NroPiso, count);
		// if (!existeP5_NLocal(id_local, P5_NroPred, p5_NroPiso, count)) {
		int tamaño_actual = existeP5_NLocal(id_local, P5_NroPred, p5_NroPiso,
				count);
		SQLiteDatabase bd = null;
		bd = util.getWritableDatabase();
		for (int i = tamaño_actual; i < count; i++) {
			ContentValues c = new ContentValues();
			c.put("id_local", id_local);
			c.put("Nro_Pred", P5_NroPred);
			c.put("P5_NroPiso", p5_NroPiso);
			c.put("P5_Ed_Nro", (i + 1));
			c.put("SwCompleto", 1);
			bd.insert("P5_N", null, c);
		}
		bd.close();
	}

	public int existeP5_NLocal(String id_local, int P5_NroPred, int P5_NroPiso,
			int count) {
		SQLiteDatabase bd = util.getReadableDatabase();
		Cursor cursor = bd
				.rawQuery(
						"SELECT * FROM P5_N WHERE id_local=? AND Nro_Pred=? AND P5_NroPiso=?",
						new String[] { id_local + "", P5_NroPred + "",
								P5_NroPiso + "" });
		int tamañoActual = cursor.getCount();
		return tamañoActual;
	}

	public List<P5_N> obtenerP5_NLocal(String id_local, int P5_NroPred,
			int P5_NroPiso) {
		List<P5_N> listObj = null;
		SQLiteDatabase bd = util.getReadableDatabase();
		Cursor cursor = bd
				.rawQuery(
						"SELECT * FROM P5_N WHERE id_local=? AND Nro_Pred=? AND P5_NroPiso=? ",
						new String[] { id_local + "", P5_NroPred + "",
								P5_NroPiso + "" });
		if (cursor.moveToFirst()) {
			listObj = new ArrayList<P5_N>();
			while (!cursor.isAfterLast()) {
				P5_N obj = new P5_N();
				obj.setId_local(id_local);
				obj.setNro_Pred(P5_NroPred);
				obj.setP5_NroPiso(P5_NroPiso);
				obj.setP5_Ed_Nro(cursor.getInt(cursor
						.getColumnIndex("P5_Ed_Nro")));
				obj.setP5_TotAmb(cursor.getInt(cursor
						.getColumnIndex("P5_TotAmb")));
				listObj.add(obj);
				cursor.moveToNext();
			}
		} else {
			bd.close();
			cursor.close();
			return null;
		}
		bd.close();
		cursor.close();
		return listObj;
	}

	// CREACION DE AMBIENTES DELLE NO BORRES!!!

	public void NoBorres(String id_local, int P5_NroPred, int P6_2_2,
			int P5_Ed_Nro, int count) {
		if (count == 99) {
			if (!existeEscalera(id_local, P5_NroPred, P6_2_2, P5_Ed_Nro, count)) {
				db = util.getWritableDatabase();
				db.delete("P6_2",
						"id_local=? and Nro_Pred=? and Nro_Ed=? and P6_2_2=?",
						new String[] { id_local + "", P5_NroPred + "",
								P5_Ed_Nro + "", P6_2_2 + "" });
				db.close();

				db = util.getWritableDatabase();
				ContentValues c = new ContentValues();
				c.put("id_local", id_local);
				c.put("Nro_Pred", P5_NroPred);
				c.put("Nro_Ed", P5_Ed_Nro);
				c.put("P6_2_1", count);
				c.put("P6_2_2", P6_2_2);
				db.insert("P6_2", null, c);
				db.close();
			}
		} else {
			for (int i = 0; i < count; i++) {
				if (!existeAmbiente(id_local, P5_NroPred, P6_2_2, P5_Ed_Nro,
						i + 1)) {
					db = util.getWritableDatabase();
					db.delete(
							"P6_2",
							"id_local=? and Nro_Pred=? and Nro_Ed=? and P6_2_2=? and P6_2_1=?",
							new String[] { id_local + "", P5_NroPred + "",
									P5_Ed_Nro + "", P6_2_2 + "", 99 + "" });
					db.close();
					db = util.getWritableDatabase();
					ContentValues c = new ContentValues();
					c.put("id_local", id_local);
					c.put("Nro_Pred", P5_NroPred);
					c.put("Nro_Ed", P5_Ed_Nro);
					c.put("P6_2_1", (i + 1));
					c.put("P6_2_2", P6_2_2);
					db.insert("P6_2", null, c);
					db.close();
				}
			}
		}
	}

	public boolean existeEscalera(String id_local, int P5_NroPred, int P6_2_2,
			int P5_Ed_Nro, int nro_ambiente) {
		db = util.getReadableDatabase();
		cursor = db
				.rawQuery(
						"select * from P6_2 where id_local=? and Nro_Pred=? and P6_2_2=? and Nro_Ed=? and P6_2_1=?",
						new String[] { id_local + "", +P5_NroPred + "",
								+P6_2_2 + "", +P5_Ed_Nro + "", +99 + "" });
		if (cursor.moveToFirst()) {
			Cerrar();
			return true;
		} else {
			Cerrar();
			return false;
		}
	}

	public boolean existeAmbiente(String id_local, int P5_NroPred, int P6_2_2,
			int P5_Ed_Nro, int nro_ambiente) {
		db = util.getReadableDatabase();
		cursor = db
				.rawQuery(
						"select * from P6_2 where id_local=? and Nro_Pred=? and P6_2_2=? and Nro_Ed=? and P6_2_1=?",
						new String[] { id_local + "", +P5_NroPred + "",
								+P6_2_2 + "", +P5_Ed_Nro + "",
								+nro_ambiente + "" });
		if (cursor.moveToFirst()) {
			Cerrar();
			return true;
		} else {
			Cerrar();
			return false;
		}
	}

	protected void Cerrar() {
		cursor.close();
		db.close();
	}

	// FIN CREACION DE AMBIENTES DELLE NO BORRES!!!

	public int cantidadEdificacionesPorPredio(String id_local, int P5_NroPred) {
		int edificaciones = 0;
		SQLiteDatabase bd = util.getReadableDatabase();
		Cursor cursor = bd.rawQuery(
				"SELECT * FROM P5 WHERE id_local=? AND Nro_Pred=?",
				new String[] { id_local + "", P5_NroPred + "" });
		if (cursor.moveToFirst()) {
			while (!cursor.isAfterLast()) {
				edificaciones = cursor
						.getInt(cursor.getColumnIndex("P5_Tot_E"));
				cursor.moveToNext();
			}
		}
		bd.close();
		cursor.close();
		return edificaciones;
	}

	public int existeRegistroFoto(String idLocal, int numeroPredio,
			int numeroPiso) {
		int cantidadRegistros = 0;
		SQLiteDatabase bd = util.getReadableDatabase();
		Cursor cursor = bd
				.rawQuery(
						"SELECT * FROM P5_F WHERE id_local=? AND Nro_Pred=? AND P5_NroPiso=?",
						new String[] { idLocal + "", numeroPredio + "",
								numeroPiso + "" });
		cantidadRegistros = cursor.getCount();
		bd.close();
		cursor.close();
		return cantidadRegistros;
	}

	public void ingresarFotosEscalaPisos(String codLocal, int numPredio,
			int numeroPiso, String nombreFoto, String escala, int version,
			int swCompleto, int swEnviado) {
		SQLiteDatabase bd = util.getWritableDatabase();
		try {
			bd = util.getWritableDatabase();
			bd.execSQL("INSERT INTO P5_F(id_local,Nro_Pred,P5_NroPiso,P5_Foto,"
					+ "P5_Escala,Version,SwCompleto,SwEnviado) VALUES('"
					+ codLocal + "'," + numPredio + "," + numeroPiso + ",'"
					+ nombreFoto + "','" + escala + "'," + version + "," + 1
					+ "," + 0 + ")");
		} catch (Exception ex) {
			String h = ex.getMessage();
		}
		bd.close();
	}

	public void actualizarP5Pisos(String codLocal, int numPredio,
			int numeroPiso, String nombreFoto, String escala, int version,
			int swCompleto, int swEnviado) {
		SQLiteDatabase bd = util.getWritableDatabase();
		ContentValues valores = new ContentValues();
		valores.put("id_local", codLocal);
		valores.put("Nro_Pred", numPredio);
		valores.put("P5_NroPiso", numeroPiso);
		valores.put("P5_Foto", nombreFoto);
		valores.put("P5_Escala", escala);
		valores.put("Version", version);
		valores.put("SwCompleto", swCompleto);
		valores.put("SwEnviado", swEnviado);

		int ind = bd.update("P5_F", valores,
				"id_local=? and Nro_Pred=? and P5_NroPiso=?", new String[] {
						codLocal + "", numPredio + "", numeroPiso + "" });
		bd.close();
	}

	public List<P5_F> obtenerP5_FLocal(String id_local, int P5_NroPred) {
		List<P5_F> listObj = null;
		SQLiteDatabase bd = util.getReadableDatabase();
		Cursor cursor = bd.rawQuery(
				"SELECT * FROM P5_F WHERE id_local=? AND Nro_Pred=? ",
				new String[] { id_local + "", P5_NroPred + "" });
		if (cursor.moveToFirst()) {
			listObj = new ArrayList<P5_F>();
			while (!cursor.isAfterLast()) {
				P5_F obj = new P5_F();
				obj.setId_local(id_local);
				obj.setNro_Pred(P5_NroPred);
				obj.setP5_NroPiso(cursor.getInt(cursor
						.getColumnIndex("P5_NroPiso")));
				obj.setP5_Foto(cursor.getString(cursor
						.getColumnIndex("P5_Foto")));
				obj.setP5_Escala(cursor.getString(cursor
						.getColumnIndex("P5_Escala")));
				listObj.add(obj);
				cursor.moveToNext();
			}
		} else {
			bd.close();
			cursor.close();
			return null;
		}
		bd.close();
		cursor.close();
		return listObj;
	}

	public String comparacionPisosRegistradoMayorIngresados(String id_local,
			int P5_NroPred, int cantidadPisosIngresados,
			int cantidadPisosRegistrados, ListadoPisosAdapter pisosAdapter) {
		String mensaje = "";
		for (int i = cantidadPisosIngresados; i < cantidadPisosRegistrados; i++) {
			eliminarPisosFinales(id_local, String.valueOf(P5_NroPred),
					cantidadPisosRegistrados, cantidadPisosIngresados);

			pisosAdapter.listaPisos.remove(pisosAdapter.listaPisos.size() - 1);
			// pisosAdapter.escala.remove(pisosAdapter.listaPisos.size() - 1);
			pisosAdapter.fotosPisos.remove(pisosAdapter.fotosPisos.size() - 1);
			pisosAdapter.notifyDataSetChanged();

		}
		return mensaje = "Se eliminaron pisos que se habia registrado demas";
	}

	public void eliminarPisosFinales(String idLocal, String numeroPredio,
			int pisosRegistrados, int pisosIngresados) {
		String[] args = new String[] { idLocal, numeroPredio };
		SQLiteDatabase db = util.getWritableDatabase();
		int P5_NroPiso = 0;
		Cursor cursor = db
				.rawQuery(
						"SELECT MAX(P5_NroPiso) FROM P5_F where id_local=? and Nro_Pred=?",
						args);
		if (cursor.moveToFirst()) {
			do {
				P5_NroPiso = Integer.parseInt(cursor.getString(0));
			} while (cursor.moveToNext());
		}
		db.execSQL("DELETE FROM P5_F WHERE id_local=? and Nro_Pred=? and "
				+ "P5_NroPiso = " + P5_NroPiso, args);

		db.execSQL("DELETE FROM P5_N WHERE id_local=? and Nro_Pred=? and "
				+ "P5_NroPiso = " + P5_NroPiso, args);

		db.execSQL("DELETE FROM P6_2 WHERE id_local=? and Nro_Pred=? and "
				+ "P6_2_2 = " + P5_NroPiso, args);

		db.execSQL("DELETE FROM P6_2_4N WHERE id_local=? and Nro_Pred=? and "
				+ "P6_2_2 = " + P5_NroPiso, args);

		db.close();
	}

	public String comparacionPisosRegistradoIgualIngresados() {
		String mensaje = "";
		return mensaje = "Ya registro la cantidad de pisos que desea ingresar";
	}

	public ArrayList<String> obtenerEscalasGuardadas(String codLocal,
			int numeroPredio) {
		ArrayList<String> escalas;
		SQLiteDatabase bd = util.getReadableDatabase();
		Cursor cursor = bd.rawQuery(
				"SELECT P5_Escala FROM P5_F WHERE id_local=? AND Nro_Pred=? ",
				new String[] { codLocal + "", numeroPredio + "" });
		if (cursor.moveToFirst()) {
			escalas = new ArrayList<String>();
			while (!cursor.isAfterLast()) {
				escalas.add(cursor.getString(cursor.getColumnIndex("P5_Escala")));
				cursor.moveToNext();
			}
		} else {
			bd.close();
			cursor.close();
			return null;
		}
		bd.close();
		cursor.close();
		return escalas;
	}

	public ArrayList<String> obtenerRutasFotosPisosGuardadas(String codLocal,
			int numeroPredio) {
		ArrayList<String> rutasFotosPisos;
		SQLiteDatabase bd = util.getReadableDatabase();
		Cursor cursor = bd.rawQuery(
				"SELECT P5_Foto FROM P5_F WHERE id_local=? AND Nro_Pred=? ",
				new String[] { codLocal + "", numeroPredio + "" });
		if (cursor.moveToFirst()) {
			rutasFotosPisos = new ArrayList<String>();
			while (!cursor.isAfterLast()) {
				rutasFotosPisos.add(cursor.getString(cursor
						.getColumnIndex("P5_Foto")));
				cursor.moveToNext();
			}
		} else {
			bd.close();
			cursor.close();
			return null;
		}
		bd.close();
		cursor.close();
		return rutasFotosPisos;
	}

	public boolean verificarEscalasFotosCompletas(String codLocal, int predio) {
		Boolean completo = false;
		List<P5_F> escalasYFotos = obtenerP5_FLocal(codLocal, predio);
		for (int i = 0; i < escalasYFotos.size(); i++) {
			if (escalasYFotos.get(i).getP5_Escala().length() == 0) {
				completo = false;
				break;
			} else {
				completo = true;
			}
		}
		if (completo) {
			return true;
		} else {
			return false;
		}
	}
}
